<?php
/**
 * Classe/WhereQueryBySelection.php
 */

namespace Operationtest\Classe;

use Zend\Session\Container;

class WhereQueryBySelection {
    
    protected $table;
    
    /**
     * Constructor
     * @param array $table
     */
    public function __construct(Array $table = array()) {
        $this->table = $table;
    }
    
    /**
     * Get all store IDs from selection
     * @param array $request
     * @return array $storeIds
     */
    public function getStoresIds($request) {
        $storeIds = array();        
        $marcheStoreTable = $this->table['marcheStoreTable'];
        foreach($request->getPost()->selectable as $storeId) {
            $tabStoreId = explode("-", $storeId);
            if(count($tabStoreId) == 1) {
                array_push($storeIds, $storeId);
            } else {
                $mStores = $marcheStoreTable->getMarcheStore($tabStoreId[1]);
                if($tabStoreId[1] == 1) {
                    $stores = $storeTable->fetchAll();
                    foreach($stores as $store) {
                        array_push($storeIds, $store->idStore);
                    }
                } else {
                    foreach($mStores as $mstore) {
                        array_push($storeIds, $mstore['idStore']);
                    }
                }
            }
        }        
        $operationSession = new Container('operationSession');
        $operationSession->storeIds = $storeIds;
        return $storeIds;
    }
    
    /**
     * Get all stores's name and markets's name
     * @param array $request
     * @return string $storeName
     */
    public function getStoresNames($request) {
        $storeName = "";
        $storeTable = $this->table['storeTable'];
        $marcheTable = $this->table['marcheTable'];
        foreach($request->getPost()->selectable as $storeId){
            $tabStoreId = explode("-", $storeId);
            if(count($tabStoreId) == 1) {
                $store = $storeTable->getStore($storeId);
                if($storeName == "")
                    $storeName .= $store->name;
                else
                    $storeName .= " - ".$store->name;
            } else {
                $marche = $marcheTable->getMarche($tabStoreId[1]);
                if($storeName == "")
                    $storeName .= $marche->libelle;
                else
                    $storeName .= " - ".$marche->libelle;
            }
        }
        return $storeName;
    }
    
    public function getTabStores($request) {
        $tabStores = array();
        $storeTable = $this->table['storeTable'];   
        $marcheStoreTable = $this->table['marcheStoreTable'];
        foreach($request->getPost()->selectable as $storeId) {
            $tabStoreId = explode("-", $storeId);
            if(count($tabStoreId) == 1) {
                $storeItem = $storeTable->getStore($storeId);
                $tabStores[$storeItem->idStore] = $storeItem->name;
            } else {
                $mStores = $marcheStoreTable->getMarcheStore($tabStoreId[1]);
                if($tabStoreId[1] == 1) {
                    $stores = $storeTable->fetchAll();
                    foreach ($stores as $store) {
                        $storeItem = $storeTable->getStore($store->idStore);
                        $tabStores[$storeItem->idStore] = $storeItem->name;
                    }
                } else {
                    foreach($mStores as $mstore) {
                        $storeItem = $storeTable->getStore($mstore['idStore']);
                        $tabStores[$storeItem->idStore] = $storeItem->name;
                    }
                }
            }
        }
        return $tabStores;
    }
    
    /**
     * Build "where" clause for dashboard
     * @param array $request
     * @param string $field
     * @param string $table
     * @return array $where
     */
    public function queryForDashboard($request, $field="orderDate", $table="saleHead") {
        $where = array();
        $periodY = array(date('Y'));
        $periodM = array(date('m'));
        $semaine = array(1,2,3,4,5,6,0);
        
        if($request->getPost()->periodY) { $periodY = $request->getPost()->periodY; }
        if($request->getPost()->periodM) { $periodM = $request->getPost()->periodM; }
        if($request->getPost()->semaine) { $semaine = $request->getPost()->semaine; }
        
        $operationSession = new Container('operationSession');
        $operationSession->periodY = $periodY;
        $operationSession->periodM = $periodM;
        $operationSession->semaine = $semaine;
        
        $whereStore = "idStore IN(".implode(',', $this->getStoresIds($request)).")";

        $yearCount = 1;
        foreach($periodY as $year) {
            if($yearCount == 1) {
                $whereMonth = " AND (( EXTRACT(YEAR FROM $field) = $year";
            } else {
                $whereMonth .= " OR ( EXTRACT(YEAR FROM $field) = $year";
            }
            
            $monthCount = 1;
            foreach($periodM as $month) {
                if($monthCount == 1) {
                    $whereMonth .= " AND ( EXTRACT(MONTH FROM $field) = $month";
                } else {
                    $whereMonth .= " OR EXTRACT(MONTH FROM $field) = $month";
                }
                
                if($monthCount == count($periodM)){
                    $whereMonth .= "))";
                    $lastmonth = $month;
                } else {
                    $monthCount++;
                }
            }
            
            if($yearCount == 1){
                $lastyear = $year;
            } 
            
            if($yearCount == count($periodY)) {
                $whereMonth .= ")";
            }
            $yearCount++;
        }
        
        $dayTab = array();
        foreach($semaine as $day):
            array_push($dayTab, ($day+1));
        endforeach;
        $dayLine = implode(',', $dayTab);
        $whereDay = " AND ( EXTRACT(YEAR FROM $field) = $lastyear";
        $whereDay .= " AND EXTRACT(MONTH FROM $field) = $lastmonth";
        $whereDay .= " AND DAYOFMONTH($field) = (SELECT MAX(DAYOFMONTH($field)) FROM $table WHERE ".$whereStore." AND ( ( EXTRACT(YEAR FROM $field) = ".$lastyear." AND EXTRACT(MONTH FROM $field) = ".$lastmonth."  AND DAYOFWEEK($field) IN (".($dayLine).") ) ) ) )"; // (DAYOFWEEK, Dimanche=1 et non 0)
        
        if(isset($request->getPost()->specific_date) && $request->getPost()->specific_date != "") {
            $whereDay = " AND $field = '".$request->getPost()->altDate."'";
            $operationSession->specific_date = $request->getPost()->altDate;
        }
        $where['month'] = $whereStore.$whereMonth;
        $where['day'] = $whereStore.$whereDay;
        return $where;
    }
    
    /**
     * Build budget "where" clause for dashboard
     * @param array $request
     * @param string $field
     * @return array $where
     */
    public function budgetQueryForTableauDeBord($request) {
        $where = array();
        $periodY = array(date('Y'));
        $periodM = array(date('m'));
        $semaine = array(1,2,3,4,5,6,0);
        
        if($request->getPost()->periodY) { $periodY = $request->getPost()->periodY; }
        if($request->getPost()->periodM) { $periodM = $request->getPost()->periodM; }
        if($request->getPost()->semaine) { $semaine = $request->getPost()->semaine; } 
        
        $where['stores'] = $this->getStoresIds($request);
        foreach($periodY as $year):
            $idx = "EXTRACT(YEAR FROM month),$year";
            foreach($periodM as $month): 
                $where['date'][$idx][] = "EXTRACT(MONTH FROM month),$month";
            endforeach;
        endforeach;
        
        return $where;
    }
    
    /**
     * Build "where" clause for scoring in dashboard
     * @param type $request
     * @param type $field
     * @return string $where
     */
    public function queryForDashboardScoring($field="orderDate") {
        $operationSession = new Container('operationSession');
//        $periodY = $operationSession->offsetGet('periodY');
//        $periodM = $operationSession->offsetGet('periodM');
//        $semaine = $operationSession->offsetGet('semaine');
        $storeIds = $operationSession->offsetGet('storeIds');
        $where = "idStore IN(".implode(',', $storeIds).")";
//        $yearCount = 1;
//        foreach($periodY as $year) {
//            if($yearCount == 1) {
//                $where .= " AND (( EXTRACT(YEAR FROM $field) = $year";
//            } else {
//                $where .= " OR ( EXTRACT(YEAR FROM $field) = $year";
//            }
//            
//            $monthCount = 1;
//            foreach($periodM as $month) {
//                if($monthCount == 1) {
//                    $where .= " AND ( EXTRACT(MONTH FROM $field) = $month";
//                } else {
//                    $where .= " OR EXTRACT(MONTH FROM $field) = $month";
//                }
//                
//                $dayCount = 1;
//                foreach($semaine as $day) {
//                    if($dayCount == 1) {
//                        $where .= " AND ( DAYOFWEEK($field) = ".($day+1);
//                    } else {
//                        $where .= " OR DAYOFWEEK($field) = ".($day+1);
//                    }
//                    
//                    if($dayCount == count($semaine)){
//                        $where .= ")";
//                    } else {
//                        $dayCount++;
//                    }
//                }
//                
//                if($monthCount == count($periodM)){
//                    $where .= "))";
//                } else {
//                    $monthCount++;
//                }
//            }
//            
//            if($yearCount == count($periodY)) {
//                $where .= ")";
//            }
//            $yearCount++;
//        }
        //var_dump($where);
        return $where;
    }
    
    /**
     * Build "where" clause month only for scoring in dashboard
     * @param type $request
     * @param type $field
     * @return string $where
     */
    public function queryForDashboardScoringMonthOnly($field="orderDate") {
        $operationSession = new Container('operationSession');
//        $periodY = $operationSession->offsetGet('periodY');
//        $periodM = $operationSession->offsetGet('periodM');
//        $semaine = $operationSession->offsetGet('semaine');
        $storeIds = $operationSession->offsetGet('storeIds');
        $where = "idStore IN(".implode(',', $storeIds).")";
//        $yearCount = 1;
//        foreach($periodY as $year) {
//            if($yearCount == 1) {
//                $where .= " AND (( EXTRACT(YEAR FROM $field) = $year";
//            } else {
//                $where .= " OR ( EXTRACT(YEAR FROM $field) = $year";
//            }
//            
//            $monthCount = 1;
//            foreach($periodM as $month) {
//                if($monthCount == 1) {
//                    $where .= " AND ( EXTRACT(MONTH FROM $field) = $month";
//                } else {
//                    $where .= " OR EXTRACT(MONTH FROM $field) = $month";
//                }
//                
//                if($monthCount == count($periodM)){
//                    $where .= "))";
//                } else {
//                    $monthCount++;
//                }
//            }
//            
//            if($yearCount == count($periodY)) {
//                $where .= ")";
//            }
//            $yearCount++;
//        }
        //var_dump($where);
        return $where;
    }
    
    /**
     * Build "where" clause for synthese
     * @param array $request
     * @param string $field
     * @return string $where
     */
    public function queryForSynthese($request, $field="orderDate") {
        $periodY = array(date('Y'));
        $periodM = array(date('m'));
        $semaine = array(1,2,3,4,5,6,0);
        
        if($request->getPost()->periodY) { $periodY = $request->getPost()->periodY; }
        if($request->getPost()->periodM) { $periodM = $request->getPost()->periodM; }
        if($request->getPost()->semaine) { $semaine = $request->getPost()->semaine; }
        
        // MISE EN SESSION DES CRITERES DANS SELECTION DU PERIMETRE
        $dataCritere = new Container('dataCritere');
        $dataCritere->getManager()->getStorage()->clear('dataCritere');

        $dataCritere->periodY = $request->getPost()->periodY;
        $dataCritere->periodM = $request->getPost()->periodM;
        $dataCritere->semaine = $request->getPost()->semaine;
        $dataCritere->specific_date = $request->getPost()->altDate;
        
        $where = "idStore IN(".implode(',', $this->getStoresIds($request)).")";
        
        $yearCount = 1;
        foreach($periodY as $year) {
            if($yearCount == 1) {
                $where .= " AND (( EXTRACT(YEAR FROM $field) = $year";
            } else {
                $where .= " OR ( EXTRACT(YEAR FROM $field) = $year";
            }
            
            $monthCount = 1;
            foreach($periodM as $month) {
                if($monthCount == 1) {
                    $where .= " AND ( EXTRACT(MONTH FROM $field) = $month";
                } else {
                    $where .= " OR EXTRACT(MONTH FROM $field) = $month";
                }
                
                $dayCount = 1;
                foreach($semaine as $day) {
                    if($dayCount == 1) {
                        $where .= " AND ( DAYOFWEEK($field) = ".($day+1);
                    } else {
                        $where .= " OR DAYOFWEEK($field) = ".($day+1);
                    }
                    
                    if($dayCount == count($semaine)){
                        $where .= ")";
                    } else {
                        $dayCount++;
                    }
                }
                
                if($monthCount == count($periodM)){
                    $where .= "))";
                } else {
                    $monthCount++;
                }
            }
            
            if($yearCount == count($periodY)) {
                $where .= ")";
            }
            $yearCount++;
        }
        return $where;
    }
    
    /**
     * Build real budget "where" clause for pilotage
     * @param array $request
     * @param string $field
     * @return array $where
     */
    public function realBudgetQueryForPilotage($request, $field) {
        $where = "";
        $storeIds = $this->getStoresIds($request);
        $periodY = array(date('Y'));
        $periodM = array(date('m'));
        $semaine = array(1,2,3,4,5,6,0);
        
        if($request->getPost()->periodY) { $periodY = $request->getPost()->periodY; }
        if($request->getPost()->periodM) { $periodM = $request->getPost()->periodM; }
        if($request->getPost()->semaine) { $semaine = $request->getPost()->semaine; }
        
        $where .= "idStore IN(".implode(',', $storeIds).")";

        $yearCount = 1;
        foreach($periodY as $year) {
            if($yearCount == 1) {
                $where .= " AND (( EXTRACT(YEAR FROM $field) = $year";
            } else {
                $where .= " OR ( EXTRACT(YEAR FROM $field) = $year";
            }
            
            $monthCount = 1;
            foreach($periodM as $month) {
                if($monthCount == 1) {
                    $where .= " AND (( EXTRACT(MONTH FROM $field) = $month";
                } else {
                    $where .= " OR ( EXTRACT(MONTH FROM $field) = $month";
                }
                
                $dayCount = 1;
                foreach($semaine as $day) {
                    if($dayCount == 1) {
                        $where .= " AND ( DAYOFWEEK($field) = ".($day+1);
                    } elseif($dayCount > 1 && $dayCount < count($semaine)) {
                        $where .= " OR DAYOFWEEK($field) = ".($day+1);
                    } elseif($dayCount == count($semaine)) {
                        $where .= " OR DAYOFWEEK($field) = ".($day+1)."))";
                    }
                    $dayCount++;
                }
                
                if($monthCount == count($periodM)) {
                    $where .= "))";
                }
                $monthCount++;
            }
            
            if($yearCount == count($periodY)) {
                $where .= ")";
            }
            $yearCount++;
        }        
        return $where;
    }
    
    /**
     * Build budget "where" clause for pilotage
     * @param array $request
     * @return array $where
     */
    public function budgetQueryForPilotage($request) {
        $where = array();
        $periodY = array(date('Y'));
        $periodM = array(date('m'));
        $semaine = array(1,2,3,4,5,6,0);
        
        if($request->getPost()->periodY) { $periodY = $request->getPost()->periodY; }
        if($request->getPost()->periodM) { $periodM = $request->getPost()->periodM; }
        if($request->getPost()->semaine) { $semaine = $request->getPost()->semaine; }
        
        $where['stores'] = $this->getStoresIds($request);
        foreach($periodY as $year):
            $idx = "EXTRACT(YEAR FROM month),$year";
            foreach($periodM as $month): 
                $where['date'][$idx][] = "EXTRACT(MONTH FROM month),$month";
            endforeach;
        endforeach;        
        return $where;
    }
    
    /**
     * Build refresh budget "where" clause for pilotage
     * @param array $request
     * @return array $where
     */
    public function budgetRefreshQueryForPilotage($request) {
        $dataCritere = new Container('dataCritere');
        
        $where = array();
        $storeIds = explode(',',$request);
        $periodY = array(date('Y'));
        $periodM = array(date('m'));
        $semaine = array(1,2,3,4,5,6,0);
        
        if($dataCritere->offsetGet('periodY')) { $periodY = $dataCritere->offsetGet('periodY'); }
        if($dataCritere->offsetGet('periodM')) { $periodM = $dataCritere->offsetGet('periodM'); }
        if($dataCritere->offsetGet('semaine')) { $semaine = $dataCritere->offsetGet('semaine'); }
        
        $where['stores'] = $storeIds;
        foreach($periodY as $year):
            $idx = "EXTRACT(YEAR FROM month),$year";
            foreach($periodM as $month): 
                $where['date'][$idx][] = "EXTRACT(MONTH FROM month),$month";
            endforeach;
        endforeach;        
        return $where;
    }
    
    /**
     * Build market budget "where" clause for pilotage
     * @param array $request
     * @return array $where
     */
    public function budgetMarcheQueryForPilotage($request) {
        $where = array();        
        $marcheIds = array();
        $withMarche = 0;
        $periodY = array(date('Y'));
        $periodM = array(date('m'));
        $semaine = array(1,2,3,4,5,6,0);
        
        if($request->getPost()->periodY) { $periodY = $request->getPost()->periodY; }
        if($request->getPost()->periodM) { $periodM = $request->getPost()->periodM; }
        if($request->getPost()->semaine) { $semaine = $request->getPost()->semaine; }
        
        foreach($request->getPost()->selectable as $storeId) {
            $tabStoreId = explode("-", $storeId);
            if(count($tabStoreId) > 1) {
                $withMarche = 1;
                array_push($marcheIds, $tabStoreId[1]);
            }
        }
        
        $where['withMarche'] = $withMarche;
        $where['marches'] = $marcheIds;
        foreach($periodY as $year):
            $idx = "EXTRACT(YEAR FROM month),$year";
            foreach($periodM as $month): 
                $where['date'][$idx][] = "EXTRACT(MONTH FROM month),$month";
            endforeach;
        endforeach;        
        return $where;
    }
    
    /**
     * Build refresh market budget "where" clause for pilotage
     * @param array $request
     * @return array $where
     */
    public function budgetMarcheRefreshQueryForPilotage($request) {
        $dataCritere = new Container('dataCritere');
        
        $where = array();
        $withMarche = 0;
        $marcheIds = explode(',',$request);
        if($request != "") { $withMarche = 1; } 
        $periodY = array(date('Y'));
        $periodM = array(date('m'));
        $semaine = array(1,2,3,4,5,6,0);
        
        if($dataCritere->offsetGet('periodY')) { $periodY = $dataCritere->offsetGet('periodY'); }
        if($dataCritere->offsetGet('periodM')) { $periodM = $dataCritere->offsetGet('periodM'); }
        if($dataCritere->offsetGet('semaine')) { $semaine = $dataCritere->offsetGet('semaine'); }
        
        $where['withMarche'] = $withMarche;
        $where['marches'] = $marcheIds;
        foreach($periodY as $year):
            $idx = "EXTRACT(YEAR FROM month),$year";
            foreach($periodM as $month): 
                $where['date'][$idx][] = "EXTRACT(MONTH FROM month),$month";
            endforeach;
        endforeach;        
        return $where;
    }
}
